﻿using System.Collections.Generic;

namespace SimplyCleverMiddleTier
{
    public static  partial class CountryProvider
    {
        private static CountryData[] countryDataCached;

        public static CountryData[] GetCountries()
        {
            if (countryDataCached != null)
                return countryDataCached;


            List<CountryData> countryData = new List<CountryData>(10);

            Db.ReadFrom("SimplyClever", false,
                command =>
                {
                    command.CommandText = "SELECT Name, Code, AddressFormatKey FROM Countries ORDER BY Code";
                },
                reader =>
                {
                    countryData.Add(
                        new CountryData()
                        {
                            Code = reader.GetValue<string>("Code"),
                            Name = reader.GetValue<string>("Name"),
                            AddressFormatInfo = new AddressFormatInfo()
                            {
                                FormatKey = reader.GetValue<string>("AddressFormatKey")
                            },
                            IsoCodes = new CountryIsoCodes()
                            {
                                AlphaTwo = reader.GetValue<string>("Code")
                            }
                        });
                });

            List<KeyValuePair> states = new List<KeyValuePair>(100);
            string previousCountry = string.Empty;

            Db.ReadFrom("SimplyClever", false,
                command =>
                {
                    command.CommandText = "SELECT c.Code AS CountryCode, cs.Code AS StateCode, cs.Name AS StateName FROM CountryStates cs JOIN Countries c ON cs.CountryId=c.Id ORDER BY c.Code ASC, cs.Code";
                },
                reader =>
                {
                    string currentCountry = reader.GetValue<string>("CountryCode");

                    if (!currentCountry.Equals(previousCountry) && !string.IsNullOrEmpty(previousCountry))
                    {
                        countryData.Find(c => c.Code.Equals(previousCountry)).States = states.ToArray();
                        states.Clear();
                    }

                    states.Add(new KeyValuePair() { Key = reader.GetValue<string>("StateCode"), Value = reader.GetValue<string>("StateName") });
                    previousCountry = currentCountry;
                });
            countryData.Find(c => c.Code.Equals(previousCountry)).States = states.ToArray();


            previousCountry = string.Empty;
            List<KeyValuePair> labels = new List<KeyValuePair>(100);

            Db.ReadFrom("SimplyClever", false,
                command =>
                {
                    command.CommandText = "SELECT c.Code AS CountryCode, af.Name AS FieldKey, al.Name AS FieldValue FROM CountryAddressFieldLabels cafl JOIN Countries c ON c.Id=cafl.CountryId JOIN AddressFields af ON af.Id=cafl.AddressFieldId JOIN AddressLabels al ON al.Id=cafl.AddressLabelId ORDER BY c.Code ASC, af.Name ASC";
                },
                reader =>
                {
                    string currentCountry = reader.GetValue<string>("CountryCode");

                    if (!currentCountry.Equals(previousCountry) && !string.IsNullOrEmpty(previousCountry))
                    {
                        countryData.Find(c => c.Code.Equals(previousCountry)).AddressFormatInfo.FieldLabels = labels.ToArray();
                        labels.Clear();
                    }

                    labels.Add(new KeyValuePair() { Key = reader.GetValue<string>("FieldKey"), Value = reader.GetValue<string>("FieldValue") });
                    previousCountry = currentCountry;
                }
            );

            countryData.Find(c => c.Code.Equals(previousCountry)).AddressFormatInfo.FieldLabels = labels.ToArray();

            previousCountry = string.Empty;

            string previousField = string.Empty;
            string previousRule = string.Empty;

            List<KeyValuePair> ruleParameters = new List<KeyValuePair>(5);
            List<Rule> rules = new List<Rule>(5);
            List<FieldRule> fieldRules = new List<FieldRule>(5);

            Db.ReadFrom("SimplyClever", false,
                command =>
                {
                    command.CommandText = @"SELECT c.Code AS CountryCode, l.Name AS FieldName, vr.Name AS RuleName, m.Name AS MessageName,  vrp.Name AS ParameterName, p.Value AS ParameterValue
	                                            FROM CountryAddressFieldValueRules r 
		                                            JOIN AddressFields l on l.Id=r.AddressFieldId 
		                                            JOIN Countries c ON c.Id=r.CountryId 
		                                            JOIN ValueRules vr ON vr.Id=r.ValueRuleId 
		                                            JOIN AddressBrokenValueRuleMessages m ON m.Id = r.AddressBrokenValueRuleMessageId
		                                            LEFT JOIN CountryAddressFieldValueRuleParameters p ON p.CountryAddressFieldValueRuleId=r.Id
		                                            LEFT JOIN ValueRuleParameters vrp ON vrp.Id= p.ValueRuleParameterId
	                                            ORDER BY
		                                           c.Code ASC, l.Name ASC, vr.Name ASC, vrp.Name ASC";
                },
                reader =>
                {
                    string currentCountry = reader.GetValue<string>("CountryCode");
                    string currentField = reader.GetValue<string>("FieldName");
                    string currentRule = reader.GetValue<string>("RuleName");

                    if (!currentCountry.Equals(previousCountry) && !string.IsNullOrEmpty(previousCountry))
                    {
                        rules.Find(r => r.Key.Equals(previousRule)).Parameters = ruleParameters.ToArray();
                        ruleParameters.Clear();

                        fieldRules.Find(f => f.FieldKey.Equals(previousField)).Rules = rules.ToArray();
                        rules.Clear();

                        countryData.Find(c => c.Code.Equals(previousCountry)).AddressFormatInfo.FieldRules = fieldRules.ToArray();
                        fieldRules.Clear();

                        ruleParameters.Clear();

                        previousRule = string.Empty;
                        previousField = string.Empty;
                    }

                    
                    if (!currentField.Equals(previousField) && !string.IsNullOrEmpty(previousField))
                    {
                        rules.Find(r => r.Key.Equals(previousRule)).Parameters = ruleParameters.ToArray();
                        ruleParameters.Clear();

                        fieldRules.Find(fr => fr.FieldKey.Equals(previousField)).Rules = rules.ToArray();
                        rules.Clear();
                    
                        previousField = string.Empty;
                        previousRule = string.Empty;
                    }

                    if (!currentRule.Equals(previousRule) && !string.IsNullOrEmpty(previousRule))
                    {
                        rules.Find(r => r.Key.Equals(previousRule)).Parameters = ruleParameters.ToArray();
                        ruleParameters.Clear();

                        previousRule = string.Empty;
                    }

                    if (!reader.IsDBNull(reader.GetOrdinal("ParameterName")))
                        ruleParameters.Add(new KeyValuePair() { Key = reader.GetValue<string>("ParameterName"), Value = reader.GetValue<string>("ParameterValue") });

                    if (string.IsNullOrEmpty(previousRule))
                        rules.Add(new Rule() { Key = reader.GetValue<string>("RuleName"), MessageKey = reader.GetValue<string>("MessageName"), Parameters = new KeyValuePair[0] });

                    if (string.IsNullOrEmpty(previousField))
                        fieldRules.Add(new FieldRule() { FieldKey = reader.GetValue<string>("FieldName") });


                    previousRule = currentRule;
                    previousField = currentField;
                    previousCountry = currentCountry;
                });

            rules.Find(r => r.Key.Equals(previousRule)).Parameters = ruleParameters.ToArray();
            fieldRules.Find(fr => fr.FieldKey.Equals(previousField)).Rules = rules.ToArray();
            countryData.Find(c => c.Code.Equals(previousCountry)).AddressFormatInfo.FieldRules = fieldRules.ToArray();

            countryDataCached = countryData.ToArray();
            return countryDataCached;
        }

    }
}
